package com.cpi.cornpp.transitcorn.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.transitcorn.dto.TransitCornDTO;
import com.cpi.cornpp.transitcorn.exception.TransitCornException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

/**
 * 
 * @description: This Class is used to handle the Transit Corn Data Access
 * @author Edgar Joao
 * 
 */
public class TransitCornDAO extends DBDAO{
	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	/** SQL List Plants */ 
	private static final String SQL_TRANSIT_CORN_LIST =  "select po_number, quantity, existence, place from vw_transit_corn where po_number not in (select po_number from po_planned_reals where purchase_order_id <> ?)";//$NON-NLS-1$
	
	
	/**
	 * This method return a list of TransitCornDTO
	 * @return
	 * @throws TransitCornException
	 */
	public List list(int id) throws TransitCornException{		
		ArrayList arrayResult = null;
		PreparedStatement preparedStatement =  null;
		TransitCornDTO transitCornDTO = null;
				
		try {
			arrayResult = new ArrayList(0);					
			preparedStatement =  prepareSQL(SQL_TRANSIT_CORN_LIST, DATASOURCE);
			preparedStatement.setInt(1, id);
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				transitCornDTO = new TransitCornDTO();				
				transitCornDTO.setPoNumber(rst.getString(1));
				transitCornDTO.setQuantity(rst.getDouble(2));
				transitCornDTO.setExistence(rst.getDouble(3));
				transitCornDTO.setPlace(rst.getString(4));
				arrayResult.add(transitCornDTO);
			}
		}catch (PoolException poolException){
			TransitCornException transitCornException = null;
			poolException.printStackTrace(System.out);
			transitCornException = new TransitCornException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw transitCornException;	
		}catch (SQLException sqlException){
			TransitCornException transitCornException = null;
			sqlException.printStackTrace(System.out);
			transitCornException= new TransitCornException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw transitCornException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return arrayResult;		
	}
		
}
